-- @author lixiyong01
-- @date 2023.03.14
-- 健康分详情表 存储/任务
-- 增加白名单管理 2023.04.09 lixiyong01

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score partition (dt=${dateSuffix}, tag='store', code='table_lifecycle')
select
    nvl(a.db_name,'-') as project,
    nvl(b.org_cn_name,'-') as team,
    nvl(a.user_name,'-') as owner_id,
    a.app_id,
    a.app_name,
    case
        -- 表已经做了极限存储或人工审核过，也直接100分
        when c.is_managed = 1 then '100,表已经做极限存储或人工审核过'

        -- 判断表是否被预删除
        when a.is_tbd=1 then '100,tbd_不扣分'

        -- 针对2GB以下的表直接给100分 2147483648=2*1024*1024*1024
        when a.filesize < 2147483648 then '100,表2GB以下'

        -- 产出时间距现在还不足10天的表，还未积累足够的访问情况信息，无法合理配置生命周期，会先给99分
        when a.produce_time > '${#date(0, 0, -10):yyyy-MM-dd#}' then '099,新表不足10天'

        -- 30天内有访问过的非分区表，认为是有保留的必要的，给100分
        when a.is_partition = 0 and a.access_last_time >= '${#date(0, 0, -30):yyyy-MM-dd#}' then '100,非分区表30天内有访问'

        -- 健康分按照建议保留的天数和实际保留的天数,只要配置生命周期给20分
        when a.is_partition = 1 and a.lifecycle <> 0 then '100,分区表已设置生命周期'
        --    concat(least(100-if(a.lifecycle-a.recommend_lifecycle>80, 80, a.lifecycle-a.recommend_lifecycle),100),',表需按照建议保留天数配置')

        -- 分区表未设置生命周期或非分区表30天无访问，直接给10分
        when a.is_partition = 0 then '010,非分区表30天无访问'
        else '010,表未设置生命周期'
    end as health_score,
    concat_ws(',',
        'filesize', cast(nvl(a.filesize,0) as string),
        'lifecycle',cast(nvl(a.lifecycle,0) as string),
        'recommend_lifecycle', cast(nvl(a.recommend_lifecycle,0) as string),
        'is_partition', cast(nvl(a.is_partition,0) as string),
        'produce_time', nvl(a.produce_time,'-'),
        'max_access_span_in_100_days', cast(nvl(a.max_access_span_in_100_days,0) as string)
    ) as data_content,
    nvl(a.filesize,1) as weight
from (
    select
        nvl(table_id,0) as app_id,
        concat(db_name,'.',tbl_name) as app_name,
        if(tbl_name rlike '^tbd_',1,0) as is_tbd,
        table_id,
        db_name,
        tbl_name,
        tbl_type,
        user_name,
        location,
        is_partition,
        dt_partition,
        lifecycle,
        hot,
        filesize,
        is_claim,
        security_level,
        importance_level,
        access_last_time,
        update_last_time,
        status,
        create_time,
        produce_time,
        case
            when max_access_span_in_100_days is NULL then 0
            else max_access_span_in_100_days
        end as max_access_span_in_100_days,
        case
            when max_access_span_in_100_days <= 4 or max_access_span_in_100_days is NULL then 7
            when max_access_span_in_100_days <= 12 then 15
            when max_access_span_in_100_days <= 30 then 33
            when max_access_span_in_100_days <= 90 then 93
            when max_access_span_in_100_days <= 180 then 183
            when max_access_span_in_100_days <= 365 then 368
            when max_access_span_in_100_days <= 731 then 1095
            else -1
        end as recommend_lifecycle
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
    where dt = '${#date(0, 0, -1):yyyyMMdd#}' and filesize > 2*1024*1024*1024
) a
left join (
    select
        username,
        realname as user_name_cn,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_name = b.username
left join (
    select item_key as app_id, item_value as expire_date, 1 as is_managed
    from hdp_lbg_supin_zplisting.dim_zp_tbl_data_dapan_dim_dict_define_all
    where dt=${#date(0,0,-1):yyyyMMdd#} and action in(0,1) and type_code='存储白名单' and item_value >= dt
) c on a.app_id = c.app_id;